if exists (select 1 from sysobjects where name = 'get_personeninplan' and type = 'P')
begin
   drop procedure get_personeninplan
   print 'Procedure: get_personeninplan deleted ...'
end
go
create procedure get_personeninplan(
  @planid            int = 55
)
as
begin
  set nocount on

   create table #prs(
       prsID         int,
       name          varchar(32),
       nachname      varchar(32),
       abteilungid   int,
       abteilungname varchar(32),
       prsschemaid   int,
       von           datetime,
       bis           datetime,
       rgb           int,
       pensum        int,
       colorname     varchar(32)
   )

   insert #prs
   select prsID         = isnull(ps.PrsID,0),
          name          = isnull(Vorname,''),
          nachname      = isnull(Nachname,''),
          abteilungid   = a.AbteilungID,
          abteilungname = a.Name,
          prsschemaid   = PrsSchemaID,
          von           = ap.Von,
          bis           = ap.Bis,
          rgb           = ps.RGB,
          pensum        = isnull(Prozent,0),
          colorname     = ColorName
     from ArbeitsPlan ap
    inner join PlanSchema ps
       on ps.SchemaID = ap.SchemaID
     left outer join Person p
       on p.PrsId = ps.PrsID
    inner join Abteilung a
       on a.AbteilungID = ps.AbteilungID
     left outer join PersonalAbteilung pab
       on pab.PrsId = p.PrsId
      and pab.AbteilungID = a.AbteilungID
      --and (pab.Status = 1 or pab.Status is null)
      and ap.Von between pab.Von and pab.Bis
      and ap.Bis between pab.Von and pab.Bis
    where ap.PlanID = @planid
    order by prsschemaid


   insert #prs
   select prsID         = p.PrsId,
          name          = Vorname,
          nachname      = Nachname,
          abteilungid   = a.AbteilungID,
          abteilungname = a.Name,
          prsschemaid   = 100,
          von           = ap2.Von,
          bis           = ap2.Bis,
          rgb           = 0,
          pensum        = 100,
          colorname     = ''
     from ArbeitsPlanzprs ap
     left outer join Person p
       on p.PrsId = ap.zprsID
    inner join Abteilung a
       on a.AbteilungID = ap.abteilungID
    inner join ArbeitsPlan ap2
       on ap2.PlanID = ap.planID
    where ap.planID = @planid

   update #prs
      set name = a.Name
     from #prs
    inner join Abteilung a
       on a.AbteilungID = #prs.abteilungid
    where #prs.prsID = 0

   select * 
     from #prs
    order by abteilungid, prsschemaid, prsID

end
go
print 'Procedure: get_personeninplan done ...'
go

grant exec on get_personeninplan to prsadmins with grant option
go
grant exec on get_personeninplan to prsusers
go

